package cn.fulong.frame.service;

public final class NativeSqlUtil {

    /**
     * 前几条
     *
     * @param sql
     * @param pageSize 行数
     * @return
     */
    public static String topSQL(int dbms, String sql, int pageSize) {
        String result = "";
        switch (dbms) {
            case 1:
                // oracle
                result = "select * from (" + sql + ") where rownum <= " + pageSize;
                break;
            case 2:
                // sqlserver
                result = sql.replaceFirst("select", "select top " + pageSize);
                break;
            case 3:
                // postgres
                result = sql.replaceFirst("select", "select top " + pageSize);
                break;
            case 4:
                // mysql
                result = sql + " limit 0," + pageSize;
                break;
            case 5:
                // 达梦
                result = sql + " limit " + pageSize;
                break;
            default:
                result = "";
        }

        return result;
    }

    /**
     * 拼装查询总量的sql语句
     *
     * @param sql
     * @return
     */
    public static String countSQL(int dbms, String sql) {
        String result = "";
        switch (dbms) {
            case 1:
                // oracle
                result = "select count(*) from (" + sql + ")";
                break;
            case 2:
                // sqlserver
                result = "select count(*) from (" + sql.split("order by")[0] + ") _table";
                break;
            case 3:
                // postgres
                result = "select count(*) from (" + sql.split("order by")[0] + ") _table";
                break;
            case 4:
                // mysql
                result = "select count(*) from (" + sql + ") _table";
                break;
            case 5:
                // 达梦
                result = "select count(*) from (" + sql + ")";
                break;
            default:
                result = "";
        }
        return result;
    }

    /**
     * 分页
     *
     * @param sql
     * @param from 起始行
     * @param to   截止行
     * @return
     */
    public static String pageSQL(int dbms, String sql, int from, int to) {
        StringBuffer result = new StringBuffer();
        String order = "";
        int i;
        int j;

        switch (dbms) {
            case 1:
                // oracle
                // result = "select * from (select temp.*,rownum num from (" + sql +
                // ") temp where rownum<=" + to + ") where num>" + from;
                result.append("select * from (select temp.*,rownum num from (");
                result.append(sql);
                result.append(") temp where rownum<=");
                result.append(to);
                result.append(") where num>");
                result.append(from);
                return result.toString();
            case 2:
                // sqlserver
                order = sql.split("order by")[sql.split("order by").length - 1];
                i = sql.indexOf("select");
                j = sql.indexOf("order by");
                result.append("select * from( select row_number() over (order by ");
                result.append(order);
                result.append(") as num, ");
                result.append(sql.substring(i + 6, j));
                result.append(" ) _table where num>");
                result.append(from);
                result.append(" and num<=");
                result.append(to);
                result.append(" order by ");
                result.append(order);
                return result.toString();
            case 3:
                // postgres
                order = sql.split("order by")[sql.split("order by").length - 1];
                i = sql.indexOf("select");
                j = sql.indexOf("order by");
                result.append("select * from( select row_number() over (order by ");
                result.append(order);
                result.append(") as num, ");
                result.append(sql.substring(i + 6, j));
                result.append(" ) _table where num>");
                result.append(from);
                result.append(" and num<=");
                result.append(to);
                result.append(" order by ");
                result.append(order);
                return result.toString();
            case 4:
                // mysql
                return sql + " limit " + from + "," + (to - from);
            case 5:
                // 达梦
                return sql + " limit " + from + "," + (to - from);
            default:
                return "";
        }
    }

}
